﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using CoreData;
using Helper;
using System.IO;
namespace ImportExport
{
    public partial class frmimporthocvien : Form
    {
        public frmimporthocvien()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog d = new OpenFileDialog();
            if (d.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                textBox1.Text = d.FileName;
            }
           
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
           Excel.Worksheet xlWorkSheet;
            Excel.Range range;

            string str;
            int rCnt = 0;
            int cCnt = 0;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(textBox1.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            var  xlWorkSheets = xlWorkBook.Worksheets ;

          //  range = xlWorkSheet.UsedRange;

            for (int i = 0; i < xlWorkSheets.Count; i++)
            {
                xlWorkSheet = (Excel.Worksheet)xlWorkSheets.get_Item(i+1);
                range = xlWorkSheet.UsedRange;
                int d = 2;
                string gio = ""; string gv = "";
                DateTime ngaydangkynhaphoc = DateTime.MinValue;
                DateTime ngaynhaphoc = DateTime.MaxValue;
                if(i==12)
                {
                
                }
                var test = (string)(range.Cells[d, 2] as Excel.Range).Value2;
                if (test == "MÃ HV")
                    d++;
                var kiemtramahociven = (range.Cells[d, 2] as Excel.Range).Value2;
               
                while(!String.IsNullOrEmpty(((string)(range.Cells[d, 2] as Excel.Range).Value2)))
                {

                    

                    long Nganhhoc ;
                    string khoahoc = "";
                    string lophoc = "";
                     string dienthoai="";
                     string giohoc = "";
                     string GV = "";
                     string Ghichu = "";
                         string diachi="";
                         int tongtien = 0;
                         int tienkhoa = 0;
                       //  string giohoc = "";
                     DateTime ngaysinh = DateTime.MinValue;

                     var mahocvien = (string)(range.Cells[d, 2] as Excel.Range).Value2;


                    if(mahocvien.Contains("-"))
                    {
                     if (mahocvien != "N")
                     {

                         var hoten = (string)(range.Cells[d, 3] as Excel.Range).Value2;
                         if (!String.IsNullOrEmpty(hoten))
                         {

                             var gioitinh = (string)(range.Cells[d, 4] as Excel.Range).Value2;
                             var trangthai = (string)(range.Cells[d, 5] as Excel.Range).Value2;
                             try
                             {
                                 var ngaysinh1 = (DateTime)(range.Cells[d, 6] as Excel.Range).Value;
                                 ngaysinh = ngaysinh1;
                             }
                             catch { }
                             try
                             {
                                 diachi = (string)(range.Cells[d, 7] as Excel.Range).Value2;
                             }
                             catch { }
                             try
                             {
                                 var sdt = (int)(range.Cells[d, 8] as Excel.Range).Value2;
                                 dienthoai = sdt.ToString();
                             }
                             catch
                             {
                                 try
                                 {
                                     var sdt = (string)(range.Cells[d, 8] as Excel.Range).Value2;
                                     if (sdt != null)
                                         dienthoai = sdt.ToString();
                                 }
                                 catch { }
                             }
                             try
                             {
                                 var sotien1 = (string)(range.Cells[d, 9] as Excel.Range).Value2;
                                 var sotien2 = (string)(range.Cells[d, 10] as Excel.Range).Value2;
                                 var sotien3 = (string)(range.Cells[d, 11] as Excel.Range).Value2;
                                 var sotien4 = (string)(range.Cells[d, 12] as Excel.Range).Value2;
                             }
                             catch { }
                             try
                             {
                                 if (test == "MÃ HV")
                                 {
                                     giohoc = (string)(range.Cells[d, 21] as Excel.Range).Value2;
                                 }
                                 else { giohoc = (string)(range.Cells[d, 13] as Excel.Range).Value2; }
                             }
                             catch { }


                             if (giohoc != null)
                                 gio = giohoc;
                             try
                             {
                                 if (test == "MÃ HV")
                                 {
                                     GV = (string)(range.Cells[d, 22] as Excel.Range).Value2;
                                 }
                                 else
                                 {
                                     GV = (string)(range.Cells[d, 14] as Excel.Range).Value2;
                                 }
                                 if (GV != null)
                                 {
                                     gv = GV;

                                 }
                             }
                             catch { }
                             try
                             {
                                 if (test == "MÃ HV")
                                 {
                                     Ghichu = (string)(range.Cells[d, 23] as Excel.Range).Value2;
                                 }
                                 else { Ghichu = (string)(range.Cells[d, 15] as Excel.Range).Value2; }
                             }
                             catch { }
                             string kenhthongtin = "";
                             try
                             {
                                 if (test == "MÃ HV")
                                 {

                                     kenhthongtin = (string)(range.Cells[d, 25] as Excel.Range).Value2;
                                 }
                                 else { kenhthongtin = (string)(range.Cells[d, 17] as Excel.Range).Value2; }
                             }
                             catch { }
                             long khoaid = 0;
                             int typeoffee = 1, giamgia = 0;
                             if (test == "MÃ HV")
                             {
                                 typeoffee = (int)(range.Cells[d, 26] as Excel.Range).Value2;
                             }
                             else
                             {
                                 typeoffee = (int)(range.Cells[d, 18] as Excel.Range).Value2;
                             }
                             if (test == "MÃ HV")
                             {
                                 giamgia = (int)(range.Cells[d, 27] as Excel.Range).Value2;
                             }
                             else
                             {
                                 giamgia = (int)(range.Cells[d, 19] as Excel.Range).Value2;
                             }
                             // ney la nam 2014 thi mo ra
                             if (mahocvien[0].ToString().Contains("S"))
                             {
                                 tongtien = (int)(range.Cells[d, 20] as Excel.Range).Value2;
                                 tienkhoa = (int)(range.Cells[d, 21] as Excel.Range).Value2;
                             }
                             var kh = mahocvien.Split('-')[0];
                             string tennganh = "";
                             for (int k = 0; k < kh.Length - 6; k++)
                             {
                                 tennganh += kh[k];
                             }

                             var nganh = RegisterService.RegisterServices.BranchService.GetOneByLINQ(c => c.Code == (tennganh.ToString()));
                             Nganhhoc = nganh.ID;



                             string codelop = kh.Substring(kh.Length - 6, 2);

                            // kh = tennganh.ToString() + kh.Substring(kh.Length - 4, 4);
                             var khoacode = Path.GetFileNameWithoutExtension(textBox1.Text).Split('_');
                             string tenkhoa = "";
                           //  if(khoacode.Length<3)
                             {
                                 tenkhoa = "K" + khoacode[1].Replace("k", "") ;
                                }
                            // else
                            // {
                               //  tenkhoa = "K" + khoacode[1][1] + khoacode[1][2];
                              //  }
                             var khoa = RegisterService.RegisterServices.CourseService.GetByCode((tenkhoa));
                             if (khoa != null)
                             {
                                 khoaid = khoa.ID;
                             }
                             else
                             {
                                 var thangnam =kh.Substring(kh.Length - 4, 4);
                                 var start = mahocvien.Split('-')[0].Substring(mahocvien.Split('-')[0].Length - 4, 4);
                                 Course s = new Course();
                                 s.Name = "Khóa " + khoacode[1].Replace("k", "");
                                 s.Code = "K" + khoacode[1].Replace("k", "");
                                 s.Month = int.Parse(thangnam[0].ToString() + thangnam[1].ToString());
                                 s.Year = int.Parse(thangnam[2].ToString() + thangnam[3].ToString());
                                 s.Active = true;
                                // if (s.Name.Contains("S"))
                                   //  s.Type = 0;
                              //   else
                                    // s.Type = 1;

                                 //neu la nam 2014 thi mo if nay ra ko thi mat dinh 33 tr
                                 if (mahocvien[0].ToString().Contains("S"))
                                 {
                                    // s.Price = tienkhoa;
                                 }
                                 else
                                 {
                                    // s.Price = 33000000;
                                 }
                                // s.BranchID = Nganhhoc;
                                // s.StartDate = DateTime.Parse("01/" + start[0] + start[1] + "/20" + start[2] + start[3]);
                                // s.EndDate = s.StartDate.AddMonths(6);
                                 string mes = "";
                                 var courseid = RegisterService.RegisterServices.CourseService.Save(s, ref mes);
                                 khoaid = courseid;
                                 khoa = s;
                             }

                             //giao vien
                             long gvid = 0;
                             var giaovien = RegisterService.RegisterServices.TeacherService.GetOneByLINQ(c => c.Code == gv);
                             if (String.IsNullOrEmpty(gv))
                             {
                                 giaovien = RegisterService.RegisterServices.TeacherService.GetOneByLINQ(c => c.Code == "DEFAULT");
                             }
                             if (giaovien != null)
                             {
                                 gvid = giaovien.ID;
                             }
                             else
                             {
                                 Teacher teach = new Teacher();
                                 teach.IndentifyNumber = "";
                                 teach.Active = true;
                                 teach.Address = "DEFAULT";

                                 teach.Code = gv;
                                 teach.Description = "";
                                 teach.Email = "";
                                 teach.Name = gv;
                                 teach.Phone = "123456";
                                 if (gv.Contains("C"))
                                     teach.Sex = "Nữ";
                                 else
                                     teach.Sex = "Nam";
                                 string mess = "";
                                 var teachid = RegisterService.RegisterServices.TeacherService.Save(teach, ref mess);
                                 gvid = teachid;
                             }

                             long lopid = 0;
                             var lop = RegisterService.RegisterServices.ClassService.GetByCode((tennganh.ToString() + codelop));


                             if (lop != null)
                             {
                                 lopid = lop.ID;
                             }
                             else
                             {
                                 var start = mahocvien.Split('-')[0].Substring(mahocvien.Split('-')[0].Length - 4, 4);
                                 Class c = new Class();
                                 c.Code = tennganh.ToString() + codelop;// mahocvien.Substring(0, 3);
                                 c.Name = tennganh.ToString() + codelop;// mahocvien.Substring(0, 3);
                                 c.Size = 0;
                                 c.CourseID = khoaid;
                                 c.Study_Time = giohoc;
                                 c.MainTeacherID = gvid;
                                 c.BranchID = Nganhhoc;

                                 if (c.Name.Contains("S"))
                                     c.Type = 0;
                                 else
                                     c.Type = 1;

                                 //neu la nam 2014 thi mo if nay ra ko thi mat dinh 33 tr
                                 if (mahocvien[0].ToString().Contains("S"))
                                 {
                                     c.Price = tienkhoa;
                                 }
                                 else
                                 {
                                     c.Price = 33000000;
                                 }
                                  
                                  c.StartDate = DateTime.Parse("01/" + start[0] + start[1] + "/20" + start[2] + start[3]);
                                 // if (mahocvien[0].ToString().Contains("S"))
                                 // {
                                     // c.EndDate = c.StartDate.AddMonths(2);
                                 // }
                                 // else
                                  {
                                      c.EndDate = c.StartDate.AddMonths(6);
                                  }

                                 string mess = "";
                                 var classid = RegisterService.RegisterServices.ClassService.Save(c, ref mess);
                                lopid = classid;
                                lop = c;
                             }


                             try
                             {
                                 string ngay = "";
                                 if (test == "MÃ HV")
                                 {
                                     ngay = (string)(range.Cells[d, 24] as Excel.Range).Value;
                                 }
                                 else
                                 {
                                     ngay = (string)(range.Cells[d, 16] as Excel.Range).Value;
                                 }

                                 if (ngay.Split(Environment.NewLine.ToCharArray()).Length > 1)
                                 {
                                     if (test == "MÃ HV")
                                     {
                                         ngaydangkynhaphoc = (DateTime)(range.Cells[d, 24] as Excel.Range).Value;
                                     }
                                     else
                                     {
                                         ngaydangkynhaphoc = (DateTime)(range.Cells[d, 16] as Excel.Range).Value;
                                     }


                                     if (test == "MÃ HV")
                                     {
                                         ngaynhaphoc = (DateTime)(range.Cells[d, 24] as Excel.Range).Value;
                                     }
                                     else
                                     {
                                         ngaynhaphoc = (DateTime)(range.Cells[d, 16] as Excel.Range).Value;
                                     }
                                 }
                                 else
                                 {
                                     ngaydangkynhaphoc = DateTime.Parse(ngay.Split(Environment.NewLine.ToCharArray())[0]);
                                     ngaynhaphoc = ngaydangkynhaphoc;
                                 }
                             }
                             catch
                             {
                                 try
                                 {
                                     if (test == "MÃ HV")
                                     {
                                         ngaydangkynhaphoc = DateTime.Parse(((string)(range.Cells[d, 24] as Excel.Range).Value).Split(Environment.NewLine.ToCharArray())[0]);
                                     }
                                     else
                                     {
                                         ngaydangkynhaphoc = DateTime.Parse(((string)(range.Cells[d, 16] as Excel.Range).Value).Split(Environment.NewLine.ToCharArray())[0]);
                                     }

                                     if (test == "MÃ HV")
                                     {
                                         ngaynhaphoc = DateTime.Parse(((string)(range.Cells[d, 24] as Excel.Range).Value).Split(Environment.NewLine.ToCharArray())[1]);
                                     }
                                     else
                                     {
                                         ngaynhaphoc = DateTime.Parse(((string)(range.Cells[d, 16] as Excel.Range).Value).Split(Environment.NewLine.ToCharArray())[1]);
                                     }

                                 }
                                 catch
                                 {
                                     ngaydangkynhaphoc = lop.StartDate;
                                     ngaynhaphoc = lop.StartDate;

                                 }
                             }



                             long status = 0;
                             if (trangthai == "NL")
                                 status = 10;
                             else if (trangthai == "BL")
                             {
                                 status = 2;
                             }
                             else if(trangthai== null)
                             {
                                 status = 11;
                             }

                             long channalid = 0;

                             if (kenhthongtin == null)
                             {
                                 channalid = 4;
                             }
                             else
                             {
                                 var channel = RegisterService.RegisterServices.ChannelInfoService.GetOneByLINQ(c => c.Code.Equals(kenhthongtin));
                                 if (channel != null)
                                 {
                                     channalid = channel.ID;
                                 }
                                 else
                                 {
                                     ChannelInfo c = new ChannelInfo();
                                     c.Code = kenhthongtin;
                                     c.Name = kenhthongtin;
                                     c.Active = true;
                                     c.Description = ""; string mess = "";
                                     var ID = RegisterService.RegisterServices.ChannelInfoService.Save(c, ref mess);
                                     channalid = ID;
                                 }
                             }

                             var student = RegisterService.RegisterServices.StudentService.GetOneByCode(mahocvien);
                             if (student == null)
                             {
                                 Student t = new Student();
                                 t.Code = mahocvien;
                                 t.Active = false;
                                 t.Address = diachi;
                                 t.AppforAdmission = true;
                                 t.Bonus = 0;
                                 t.ClassID = lopid;
                                 t.CMND = true;
                                 t.ContractEdu = true;
                                 t.ChannelInfoID = channalid;
                                 t.DateOfAdmission = ngaynhaphoc;
                                 t.DateOfBirth = ngaysinh;
                                 // if (Ghichu == null)
                                 // t.Discount = 0;
                                 // else
                                 if (giamgia.ToString().Length <= 3)
                                 {
                                     t.Discount = giamgia; //int.Parse(Ghichu.Replace("giảm", "").Replace(" ", "").Replace("%", "").Replace("Tạmnghĩ", "").Replace("\n", ""));
                                 }
                                 else
                                 {
                                     t.Bonus = giamgia;
                                 }
                                 t.Email = "";
                                 t.FirstName = hoten.Split(' ')[0];
                                 for (int t1 = 1; t1 < hoten.Split(' ').Count(); t1++)
                                 {
                                     t.LastName += hoten.Split(' ')[t1] + " ";
                                 }
                                 t.LastName = t.LastName.Remove(t.LastName.Length - 1, 1);
                                 t.Name = t.FirstName + " " + t.LastName;
                                 t.Note = Ghichu;
                                 t.PlaceOfBirth = diachi;
                                 t.Phone = dienthoai.ToString();
                                 t.RegisterDate = ngaydangkynhaphoc;
                                 if (gioitinh == "Nữ")
                                     t.Sex = false;
                                 else
                                     t.Sex = true;
                                 t.SpecifiedTable = true;
                                 t.Image4x6 = true;
                                 t.Status = status;
                                 var _class = RegisterService.RegisterServices.ClassService.GetByID(lopid) as  Class;
                                 int Fee = _class.Price;// 0;// course.Price;

                                 t.TypeOfFee = typeoffee;
                                 if (typeoffee == 2 || typeoffee == 4)
                                 {
                                     Fee += ValueDefine.donghailan;
                                 }
                                 if (typeoffee == 6)
                                 {
                                     Fee += ValueDefine.dongsaulan;
                                 }
                                 Fee = Fee - ((Fee / 100) * t.Discount);
                                 Fee = Fee - t.Bonus;
                               //  neu la nam 2014 thi mo if nay ra ko thi dong lai
                                 if (mahocvien[0].ToString().Contains("S"))
                                 {
                                     t.TotalFee = tongtien;
                                 }
                                 else
                                 {
                                     t.TotalFee = Fee;
                                 }
                                 t.IsCompleted = true;
                                 t.InfoStatus = true;
                                 string mess = "";
                                 var stid = RegisterService.RegisterServices.StudentService.Save(t, ref mess);

                                 //neu import <2014 thi mo ra
                                 //Student_Fee stfee = RegisterService.RegisterServices.Student_FeeService.GetByStudentID(stid);
                                 //stfee.Description = "";
                                 //stfee.Money = t.TotalFee;
                                 //stfee.Status = 0;
                                 //stfee.StudentID = stid;
                                 //mess = "";
                                 //RegisterService.RegisterServices.Student_FeeService.Save(stfee);
                             }
                             else
                             {
                                 student.DateOfAdmission = ngaynhaphoc;
                                 student.ChannelInfoID = channalid;
                                 student.Note = Ghichu;
                                 student.Status = status;
                                
                                 //neu la 2014 ma >thang 4 thu = false
                                 student.IsCompleted = false;

                                 var _class = RegisterService.RegisterServices.ClassService.GetByID(lopid) as Class;
                                 int Fee = _class.Price;// 0;// course.Price;

                                 student.TypeOfFee = typeoffee;
                                 if (typeoffee == 2 || typeoffee == 4)
                                 {
                                     Fee += ValueDefine.donghailan;
                                 }
                                 if (typeoffee == 6)
                                 {
                                     Fee += ValueDefine.dongsaulan;
                                 }
                                 Fee = Fee - ((Fee / 100) * student.Discount);
                                 Fee = Fee - student.Bonus;
                                 //  neu la nam 2014 thi mo if nay ra ko thi dong lai
                                 if (mahocvien[0].ToString().Contains("S"))
                                 {
                                     student.TotalFee = tongtien;
                                 }
                                 else
                                 {
                                     student.TotalFee = Fee;
                                 }

                                 string mess = "";
                                  RegisterService.RegisterServices.StudentService.Save(student, ref mess);
                             }

                         }
                     }
                }
                  

                    d++;
                }
               
            }
            xlWorkBook.Close(true, null, null);
            xlApp.Quit();
        }
    }
}
